package com.cxm.report.dao.provider;

import com.cxm.report.model.IReport;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

/**
 * @author scl.BlueOcean
 * @version 1.0
 * @date 2018年07月04 14:02
 * @description
 */
public class ChannelReportProvider {
    private final String TBL_REPORT = "t_channel_report";
    private final String TBL_CHANNEL = "app_key_v1";
    private final String TBL_ORDER = "order_v1";
    private final String TBL_ORDER_RESULT = "order_result_v1";

    public String delete(String date){
        SQL sql = new SQL().DELETE_FROM(TBL_REPORT);
        if (StringUtils.hasText(date)) {
            sql.WHERE("report_date=#{date}");
        }
        return sql.toString();
    }
    /**
     * 获取渠道数据
     * */
    public String getChannelInfo(String date){
        StringBuffer sql = new StringBuffer("insert into ");
        sql.append(TBL_REPORT).append("(channelName,report_date,requestNum)")
                .append("select app_name,date(o.create_date) odate,count(1) from ")
                .append(TBL_CHANNEL)
                .append(" a join ")
                .append(TBL_ORDER)
                .append(" o on o.user_id=a.id");

        if (StringUtils.hasText(date)) {
            sql.append(" and date(o.create_date)=#{date}");
        }
        sql.append(" group by app_name,odate");
        return sql.toString();
    }
    /**
     * 更新反馈次数及平均反馈时间
     * */
    public String updateBackInfo(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_REPORT).append(" cr,")
            .append(" (select app_name,count(1) num,date(o.create_date) odate,avg(TIMESTAMPDIFF(SECOND,o.create_date,r.create_date)) qt  from ")
            .append(TBL_CHANNEL).append(" a ")
            .append("join ").append(TBL_ORDER).append(" o ")
            .append("on o.user_id=a.id ");
        if (StringUtils.hasText(date)) {
            sql.append(" and date(o.create_date)=#{date}");
        }
        sql.append(" join ").append(TBL_ORDER_RESULT).append(" r on r.order_no=o.order_no")
            .append(" group by app_name,odate) s")
            .append(" set cr.backNum = s.num,cr.avgBackTime = s.qt ")
            .append(" where cr.channelName = s.app_name and cr.report_date=s.odate");
        return sql.toString();
    }
    /**
     * 更新查询成功次数及成功平均反馈时间
     * */
    public String updateSuccessInfo(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_REPORT).append(" cr,")
            .append(" (select app_name,count(1) num,date(o.create_date) odate,avg(TIMESTAMPDIFF(SECOND,o.create_date,r.create_date)) qt  from ")
            .append(TBL_CHANNEL).append(" a ")
            .append("join ").append(TBL_ORDER).append(" o ")
            .append("on o.user_id=a.id");
        if (StringUtils.hasText(date)) {
            sql.append(" and date(o.create_date)=#{date}");
        }
        sql.append(" join ").append(TBL_ORDER_RESULT)
            .append(" r on r.order_no=o.order_no and r.result_code =")
            .append(IReport.SUCCESS)
            .append(" group by app_name,odate) s")
            .append(" set cr.successNum = s.num,cr.avgSuccessTime = s.qt ")
            .append(" where cr.channelName = s.app_name and cr.report_date=s.odate");
        return sql.toString();
    }

    /**
     * 更新失败数
     * @return
     */
    public String updateFailedNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_REPORT).append(" cr,")
                .append(" (select app_name,count(1) num,date(o.create_date) odate from ")
                .append(TBL_CHANNEL).append(" a ")
                .append("join ").append(TBL_ORDER).append(" o ")
                .append("on o.user_id=a.id");
        if (StringUtils.hasText(date)) {
            sql.append(" and date(o.create_date)=#{date}");
        }
        sql.append(" join ").append(TBL_ORDER_RESULT)
                .append(" r on r.order_no=o.order_no and r.result_code in (")
                .append(IReport.FAILED).append(",").append(IReport.WEI_HU).append(")")
                .append(" group by app_name,odate) s")
                .append(" set cr.failedNum = s.num ")
                .append(" where cr.channelName = s.app_name and cr.report_date=s.odate");
        return sql.toString();
    }
    /**
     * 更新查询超时数
     * @return
     */
    public String updateOverTimeNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_REPORT).append(" cr,")
                .append(" (select app_name,count(1) num,date(o.create_date) odate from ")
                .append(TBL_CHANNEL).append(" a ")
                .append("join ").append(TBL_ORDER).append(" o ")
                .append("on o.user_id=a.id");
        if (StringUtils.hasText(date)) {
            sql.append(" and date(o.create_date)=#{date}");
        }
        sql.append(" join ").append(TBL_ORDER_RESULT)
                .append(" r on r.order_no=o.order_no and r.result_code in (")
                .append(IReport.OVER_TIME).append(",").append(IReport.OVER_TIME_GIVE_UP).append(")")
                .append(" group by app_name,odate) s")
                .append(" set cr.overTimeNum = s.num ")
                .append(" where cr.channelName = s.app_name and cr.report_date=s.odate");
        return sql.toString();
    }
    /**
     * 更新查无记录数
     * @return
     */
    public String updateNoRecordNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_REPORT).append(" cr,")
                .append(" (select app_name,count(1) num,date(o.create_date) odate from ")
                .append(TBL_CHANNEL).append(" a ")
                .append("join ").append(TBL_ORDER).append(" o ")
                .append("on o.user_id=a.id");
        if (StringUtils.hasText(date)) {
            sql.append(" and date(o.create_date)=#{date}");
        }
        sql.append(" join ").append(TBL_ORDER_RESULT)
                .append(" r on r.order_no=o.order_no and r.result_code=")
                .append(IReport.NO_RECORD)
                .append(" group by app_name,odate) s")
                .append(" set cr.noRecordNum = s.num ")
                .append(" where cr.channelName = s.app_name and cr.report_date=s.odate");
        return sql.toString();
    }
    /**
     * 更新查询中个数
     * @return
     */
    public String updateInquiryNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_REPORT).append(" cr,")
                .append(" (select app_name,count(1) num,date(o.create_date) odate from ")
                .append(TBL_CHANNEL).append(" a ")
                .append("join ").append(TBL_ORDER).append(" o ")
                .append("on o.user_id=a.id");
        if (StringUtils.hasText(date)) {
            sql.append(" and date(o.create_date)=#{date}");
        }
        sql.append(" and o.order_no not in (  select DISTINCT order_no from order_result_v1 )")
                .append(" group by app_name,odate) s")
                .append(" set cr.inquiryNum = s.num ")
                .append(" where cr.channelName = s.app_name and cr.report_date=s.odate");
        return sql.toString();
    }
}
